package com.pur.zsms.foodmanage.food.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.foodmanage.food.dao.IFoodDao;
import com.pur.zsms.foodmanage.food.po.FoodMaterPo;
import com.pur.zsms.foodmanage.food.po.FoodPo;
import com.pur.zsms.foodmanage.food.po.FoodTypePo;
import com.pur.zsms.foodmanage.food.vo.FoodMaterVo;
import com.pur.zsms.foodmanage.food.vo.FoodTypeVo;
import com.pur.zsms.foodmanage.food.vo.FoodVo;
import com.pur.zsms.pmsnmanage.menu.po.MenuPo;
import com.pur.zsms.pmsnmanage.role.vo.RoleVo;
import com.pur.zsms.pmsnmanage.user.dao.IUserDao;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.po.UserRolePo;
import com.pur.zsms.pmsnmanage.user.vo.UserVo;
import com.pur.zsms.po.CashierOrderDetailPo;
import com.pur.zsms.po.CashierOrderPo;
import com.pur.zsms.po.ShoppingCartCashierPo;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;
import com.pur.zsms.vo.ShoppingCartCashierVo;

@Repository("foodDao")
public class FoodDaoImpl extends GenericDao implements IFoodDao {

	public PaginationSupport listFood(PageController pageController, FoodVo foodVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT f.food_id, f.food_name, SUM( ml.mater_value * mater_weight * ml.mater_proportion * 0.01 ), f.food_type, food_type1, food_mtype, food_mname, food_mweight, GROUP_CONCAT( CONCAT( ( CONCAT_WS( ':', m.mater_name, mf.mater_weight ) ), 'g' ) ) FROM t_food_mater mf, t_food f, t_material m, t_material_level ml, t_user_supplier s WHERE f.food_id = mf.food_id AND m.mater_id = mf.mater_id AND mf.mater_id = ml.mater_id AND ml.mater_level = 1 AND s.user_id = '"
				+ foodVo.getUserId() + "' AND s.sup_type = m.mater_type AND s.sup_id = ml.sup_id ");

		if (foodVo.getFoodType1() != 0) {
			ql.append(" and food_type1 = '");
			ql.append(foodVo.getFoodType1());
			ql.append("'");
		}
		if (foodVo.getFoodName() != null && !foodVo.getFoodName().equals("")) {
			ql.append(" and food_name like '%");
			ql.append(foodVo.getFoodName());
			ql.append("%'");
		}
		if (foodVo.getFoodMtype() != null && !foodVo.getFoodMtype().equals("")) {
			ql.append(" and food_mtype like '%");
			ql.append(foodVo.getFoodMtype());
			ql.append("%'");
		}
		if (foodVo.getFoodMname() != null && !foodVo.getFoodMname().equals("")) {
			ql.append(" and food_mname like '%");
			ql.append(foodVo.getFoodMname());
			ql.append("%'");
		}
		ql.append("AND EXISTS (SELECT 1 FROM t_code WHERE (SELECT COUNT(1) FROM t_food_mater fm1 WHERE fm1.food_id = f.food_id) = (SELECT COUNT(1) FROM t_food_mater fm2, t_material_level ml2, t_user_supplier s2 , t_material m2 WHERE m2.mater_id=ml2.mater_id AND fm2.food_id =f.food_id AND fm2.mater_id=m2.mater_id AND ml2.mater_level = 1 AND s2.user_id=s.user_id AND s2.sup_type = m2.mater_type AND s2.sup_id = ml2.sup_id ) )");
		ql.append(" GROUP BY f.food_id ");
		ql.append(" order by f.food_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listFoodForCashier(PageController pageController, FoodVo foodVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT f.`food_id`,f.`food_name`,f.`food_value` FROM t_food f WHERE f.`shop_id` = '" + foodVo.getShopId() + "' ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public List<Object[]> listFoodForCashier(FoodVo foodVo) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT f.`food_id`,f.`food_name`,f.`food_value` FROM t_food f WHERE f.`shop_id` = '" + foodVo.getShopId() + "' ");
		if (foodVo.getFoodType() != null && !"".equals(foodVo.getFoodType())) {
			sql.append(" AND f.food_type = '" + foodVo.getFoodType() + "'  ");
		}
		if (!"".equals(foodVo.getFoodName()) && foodVo.getFoodName() != null) {
			sql.append(" AND f.food_name like '%" + foodVo.getFoodName() + "%'  ");
		}

		sql.append(" AND f.`is_active`=0  ");

		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public PaginationSupport listFoodIndex(PageController pageController, FoodVo foodVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT f.food_id,f.food_name,f.food_type,f.food_type1,f.food_mtype,f.food_mname FROM t_food f  where 1=1 ");

		if (foodVo.getFoodType1() != 0) {
			ql.append(" and food_type1 = '");
			ql.append(foodVo.getFoodType1());
			ql.append("'");
		}
		if (foodVo.getFoodName() != null && !foodVo.getFoodName().equals("")) {
			ql.append(" and food_name like '%");
			ql.append(foodVo.getFoodName());
			ql.append("%'");
		}
		if (foodVo.getFoodMtype() != null && !foodVo.getFoodMtype().equals("")) {
			ql.append(" and food_mtype like '%");
			ql.append(foodVo.getFoodMtype());
			ql.append("%'");
		}
		if (foodVo.getFoodMname() != null && !foodVo.getFoodMname().equals("")) {
			ql.append(" and food_mname like '%");
			ql.append(foodVo.getFoodMname());
			ql.append("%'");
		}
		ql.append(" order by f.food_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listFoodMaterDetailed(PageController pageController, FoodMaterVo foodMaterVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT f.fm_id, f.food_id, a.food_name, f.mater_id, b.mater_name, f.mater_weight FROM t_food_mater f, t_food a, t_material b WHERE f.food_id=a.food_id AND f.mater_id = b.mater_id  and f.food_id = '"
				+ foodMaterVo.getFoodId() + "'");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findShoppingCartCashier(PageController pageController, ShoppingCartCashierVo c) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT o.`cid`,o.`cart_id`,o.`food_id`,o.`food_name`,o.`food_value`,o.`food_value_all`,o.`shop_id`,o.number FROM t_shopping_cart_cashier o WHERE 1=1 ");
		sql.append(" AND o.shop_id = '" + c.getShopId() + "' ");
		sql.append(" AND o.cart_id = '" + c.getCartId() + "'");
		return this.findByNativeQuery(sql.toString(), pageController);
	}

	public PaginationSupport getShoppingCartOrderTable(PageController pageController, ShoppingCartCashierVo c) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT o.cart_id,GROUP_CONCAT( '商品名称：',o.food_name, '&nbsp&nbsp&nbsp&nbsp单价：', o.food_value, '&nbsp&nbsp&nbsp数量：', o.number,  '<br/>' SEPARATOR '' ) FROM t_shopping_cart_cashier o WHERE   o.cart_Id!=0 AND o.shop_Id = '"
				+ c.getShopId() + "' GROUP BY o.cart_id");
		return this.findByNativeQuery(sql.toString(), pageController);
	}

	public List<FoodPo> findFoodPo(FoodVo foodVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from FoodPo o where o.foodName='" + foodVo.getFoodName() + "'");
		List<FoodPo> list = template.find(ql.toString(), null);
		return list;
	}

	public int addFood(FoodPo c) throws GenericException {
		this.merge(c);
		List quert = this.entityManager.createNativeQuery("SELECT LAST_INSERT_ID()").getResultList();
		return Integer.valueOf(quert.get(0).toString());
	}

	public void addCashierOrderPo(CashierOrderPo c) throws GenericException {
		this.persist(c);
	}

	public void addCashierOrderDetailPo(CashierOrderDetailPo c) throws GenericException {
		this.persist(c);
	}

	public void addFoodType(FoodTypePo c) throws GenericException {
		this.persist(c);
	}

	public void updateFood(FoodPo c) throws GenericException {
		this.merge(c);
	}

	public void updateShoppingCartCashierPo(ShoppingCartCashierPo c) throws GenericException {
		this.merge(c);
	}

	public void addFoodMater(FoodMaterPo c) throws GenericException {
		this.persist(c);
	}

	public void addShoppingCartCashierPo(ShoppingCartCashierPo c) throws GenericException {
		this.persist(c);
	}

	public void updateUser(UserPo c) throws GenericException {
		UserPo oldUserPo = this.listUserByUserId(String.valueOf(c.getUserId()));
		c.setInsertTime(oldUserPo.getInsertTime());
		this.merge(c);
	}

	public void deleteFood(int foodId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete FoodPo o where o.foodId=" + foodId);
		template.executeUpdate(ql.toString());

		JpaTemplate template2 = this.getTemplate();
		StringBuffer ql2 = new StringBuffer();
		ql2.append(" delete FoodMaterPo o where o.foodId=" + foodId);
		template2.executeUpdate(ql2.toString());
	}

	public void deleteShoppingCartCashierPo(ShoppingCartCashierVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShoppingCartCashierPo o where o.cartId='" + c.getCartId() + "' AND o.shopId ='" + c.getShopId() + "'");
		if (c.getFoodId() != 0) {
			ql.append(" AND o.foodId = '" + c.getFoodId() + "'");
		}

		template.executeUpdate(ql.toString());
	}

	public void deleteFoodMaterPo(int foodId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete FoodMaterPo o where o.foodId=" + foodId);
		template.executeUpdate(ql.toString());
	}

	public FoodPo listFoodByFoodName(String foodId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		FoodPo c = new FoodPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from FoodPo o where o.foodId=" + foodId);
		List<FoodPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public List<FoodPo> getFoodPo(FoodVo vo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		FoodPo c = new FoodPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from FoodPo o where o.foodId='" + vo.getFoodId() + "' AND o.shopId ='" + vo.getShopId() + "'");
		List<FoodPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserRolePo> listUserRoleByUserId(String userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UserRolePo o where o.userId=" + userId);
		List<UserRolePo> list = template.find(ql.toString(), null);
		return list;
	}

	public void updateUserRole(UserRolePo userRolePo) throws GenericException {
		this.merge(userRolePo);
	}

	public void deleteUserRole(String userRoleId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UserRolePo o where o.userRoleId=" + userRoleId);
		template.executeUpdate(ql.toString());
	}

	public void deleteUserRoleByUserId(String userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UserRolePo o where o.userId=" + userId);
		template.executeUpdate(ql.toString());
	}

	public UserPo findUserLogon(String userName, String password) throws GenericException {
		UserPo user = null;
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UserPo o where o.userName='" + userName + "'");
		ql.append(" and o.password='" + password + "'");
		List<UserPo> list = template.find(ql.toString(), null);
		if (list.size() > 0) {
			user = list.get(0);
		}
		return user;
	}

	public List<MenuPo> getUserMenuList(String userId) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select * from t_menu pv");
		ql.append(" where pv.menu_id in (select rolepv.menu_id");
		ql.append(" from t_role_menu rolepv, t_user_role ur, t_role role");
		ql.append(" where ur.role_id = role.role_id");
		ql.append(" and ur.user_id = " + userId);
		ql.append("  and ur.role_id= rolepv.role_id");
		ql.append(" and role.is_active='Y')");
		ql.append(" order by pv.menu_id");
		JpaTemplate template = this.getTemplate();
		Query query = this.entityManager.createNativeQuery(ql.toString(), MenuPo.class);
		List<MenuPo> list = query.getResultList();
		this.entityManager.close();
		// List<MenuPo> list = template.find(ql.toString(), null);

		return list;
	}

	@Override
	public UserPo listUserByUserId(String userId) throws GenericException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Object[]> getFoodMaterId(String foodId) throws GenericException {
		/*
		 * JpaTemplate template = this.getTemplate(); StringBuffer ql = new
		 * StringBuffer(); ql.append(" SELECT mater_id FROM  t_food_mater ");
		 * ql.append(" where food_id=" + foodId); List list =
		 * template.find(ql.toString(), null);
		 */
		List list = this.entityManager.createNativeQuery(" SELECT mater_id,mater_weight FROM  t_food_mater where food_id='" + foodId + "'")
				.getResultList();
		return list;
	}

	public List<FoodTypePo> getFoodType(FoodTypeVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from FoodTypePo o where o.shopId=" + c.getShopId());

		if (c.getFoodName() != null && !"".equals(c.getFoodName())) {
			ql.append(" AND EXISTS ( SELECT 1 FROM FoodPo a WHERE a.shopId=o.shopId AND a.foodType=o.foodTypeId AND a.foodName LIKE '%"
					+ c.getFoodName() + "%' )");
		}
		List<FoodTypePo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShoppingCartCashierPo> getShoppingCartCashierPo(ShoppingCartCashierVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from ShoppingCartCashierPo o where o.cartId='" + c.getCartId() + "' AND o.shopId = '" + c.getShopId() + "' ");
		if (c.getFoodId() != 0) {
			ql.append(" AND o.foodId = '" + c.getFoodId() + "' ");
		}
		List<ShoppingCartCashierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object[]> getShoppingCartCasherTotalValue(ShoppingCartCashierVo c) throws GenericException {
		List list = this.entityManager.createNativeQuery(
				"SELECT SUM(food_value_all) FROM t_shopping_cart_cashier WHERE cart_id = '" + c.getCartId() + "' AND shop_id ='" + c.getShopId()
						+ "' ").getResultList();
		return list;
	}

	public List<Object[]> getCartOrderNumber(long shopId) throws GenericException {
		List list = this.entityManager.createNativeQuery(
				"SELECT  COUNT(DISTINCT  cart_id) FROM t_shopping_cart_cashier o WHERE  o.`shop_id`='" + shopId + "' AND  cart_id !=0")
				.getResultList();
		return list;
	}

}
